﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using Mono.Data.Sqlite;
using UnityEngine;
/*
 * 查询语句状态下  SqliteCommand 可以使用dispose进行释放
 * 非查询状态下  使用 SqliteCommand 的dispose进行释放 会把 和数据库的连接断开
 */
public static class SqlDbCommand// : SqlDbConnect
{
   // private SqliteCommand _sqlComm;

    // public SqlDbCommand(string dbPath) : base(dbPath)
    // {
    //     _sqlComm = new SqliteCommand(_sqliteConn);
    // }
    
    /// <summary>
    /// 执行数据库语句
    /// </summary>
    /// <param name="sqlStr"></param>
    /// <returns></returns>
    public static int ExecuteSqliteStr(this SqlDbConnect _sqlDbConn,  string sqlStr)
    {
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        _sqlComm.CommandText = sqlStr;
        var res = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return res;
    }

    #region 表管理

    /// <summary>
    /// 根据指定类型 的属性列表 创建表 
    /// </summary>
    /// <typeparam name="T">数据类 创建成表名</typeparam>
    /// <returns></returns>
    public static  int CreateTable<T>(this SqlDbConnect _sqlDbConn) where T : BaseSqliteData
    {
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        if (_sqlDbConn.IsTableCreated<T>())
            return -1;
        var type = typeof(T);
        string typeName = type.Name;
        StringBuilder sb = new StringBuilder();

        sb.Append($"create table {typeName}(");

        //获取类中的所有属性
        PropertyInfo[] properties = type.GetProperties();
        foreach (PropertyInfo property in properties)
        {
            var qqq= typeof(float).Name;
            ModelHelp attribute = property.GetCustomAttribute<ModelHelp>();
            if (attribute == null)
                continue;
            if (attribute.IsCreated)
            {
                var temp = property.PropertyType.Name;
                if (attribute.useProperticeDefault) //使用属性本身的名字和类型
                {
                    //TODO C# 数据类型 和 Sqlite 数据类型 对应关系 应该从这里进行对应 确认
                    //TODO 定义 和读取解析定义的要一致
                    //属性名 属性类型 ，
                    if (property.GetType().ToString() == typeof(float).Name)
                    {
                        sb.Append($"{property.Name} {typeof(Single).Name}");
                    }
                    else
                    {
                        sb.Append($"{property.Name} {property.PropertyType.Name}");
                    }
                    // sb.Append($"{property.Name} {property.PropertyType.Name}");
                }
                else //使用自定义 名称和类型
                {
                    if (attribute.Type =="float")
                    {
                        sb.Append($"{property.Name} {typeof(Single)}");
                    }
                    else
                    {
                        sb.Append($"{attribute.FieldName} {attribute.Type}");
                    }
                    //sb.Append($"{attribute.FieldName} {attribute.Type}");
                }

                //如果是主键
                if (attribute.IsPrimaryKey)
                {
                    sb.Append(" primary key ");
                }

                if (attribute.IsCanNull)
                {
                    sb.Append(" null ");
                }
                else
                {
                    sb.Append(" not null ");
                }

                sb.Append(",");
            }
        }

        sb.Remove(sb.Length - 1, 1); //去除最后一个逗号
        sb.Append(")"); //将最后的半边括号追加上去
        _sqlComm.CommandText = sb.ToString();
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    /// <summary>
    /// 删除指定类型的表
    /// </summary>
    /// <typeparam name="T">数据类</typeparam>
    /// <returns></returns>
    public static int DeleteTable<T>(this SqlDbConnect _sqlDbConn) where T : BaseSqliteData
    {
        if (_sqlDbConn.IsTableCreated<T>())
        {
            Debug.LogError("该表不存在");
            return -1;
        }
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        string sqlStr = $"drop table {typeof(T).Name}";
        _sqlComm.CommandText = sqlStr;
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    /// <summary>
    /// 判断该表是否已经创建过了
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static bool IsTableCreated<T>(this SqlDbConnect _sqlDbConn) where T : BaseSqliteData
    {
        string sql = $"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{typeof(T).Name}'";
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        
        _sqlComm.CommandText = sql;
        SqliteDataReader dr= _sqlComm.ExecuteReader();
        if (dr != null && dr.Read())
        {
            bool temp = Convert.ToInt32(dr[dr.GetName(0)]) == 1;
            _sqlComm.Dispose();
            return temp;
        }
        _sqlComm.Dispose();
        return false;
    }

    #endregion

    #region 新增数据

    //新增
    /// <summary>
    /// 新增数据  插入数据
    /// </summary>
    /// <param name="t">数据实例对象</param>
    /// <typeparam name="T">表数据类</typeparam>
    /// <returns></returns>
    public static int Insert<T>(this SqlDbConnect _sqlDbConn,T t) where T : BaseSqliteData
    {
        if (t == default(T))
        {
            Debug.LogError("Insert()  参数错误！！！");
            return -1;
        }
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        
        Type type = typeof(T);
        StringBuilder sb = new StringBuilder();
        sb.Append($"INSERT INTO {type.Name} (");
        PropertyInfo[] propertyInfos = type.GetProperties();
        foreach (PropertyInfo p in propertyInfos)
        {
            ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
            if (attribute == null)
                continue;
            if (attribute.IsCreated) //如果这个属性可以在数据库中创建
            {
                if (attribute.useProperticeDefault) //使用属性本身的名字和类型
                {
                    //属性名 属性类型 ，
                    sb.Append(p.Name + ",");
                }
                else //使用自定义 名称和类型
                {
                    sb.Append(attribute.FieldName + ",");
                }
            }
        }

        sb.Remove(sb.Length - 1, 1);
        sb.Append(") VALUES (");
        //设置对应的值
        foreach (PropertyInfo p in propertyInfos)
        {
            ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
            if (attribute == null)
                continue;
            if (attribute.IsCreated)
            {
                if (attribute.useProperticeDefault)
                {
                    // var valueType= p.GetValue(t).GetType();
                    var valueType= p.PropertyType;
                   
                    if (valueType == typeof(string)|| valueType == typeof(char))
                    {
                        sb.Append($"'{p.GetValue(t)}'");
                    }
                    else
                    {
                        sb.Append(p.GetValue(t)); //获取指定对象的属性值
                    }
                }
                else
                {
                    if (attribute.Type == "string" || attribute.Type == "char")
                    {
                        sb.Append($"'{p.GetValue(t)}'");
                    }
                    else
                    {
                        sb.Append(p.GetValue(t)); //获取指定对象的属性值
                    }
                }
                
               

                sb.Append(",");
            }
        }

        sb.Remove(sb.Length - 1, 1);
        sb.Append(")");
        _sqlComm.CommandText = sb.ToString();
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    /// <summary>
    /// 给数据库对应类型的表中 添加多组数据
    /// </summary>
    /// <param name="tList">数据集合实例</param>
    /// <typeparam name="T">对应表的数据类型</typeparam>
    /// <returns></returns>
    public static int Insert<T>(this SqlDbConnect _sqlDbConn,List<T> tList) where T : BaseSqliteData
    {
        if (tList == null || tList.Count == 0)
        {
            Debug.LogError("Insert()  参数错误！！！");
            return -1;
        }

      //   Type type = typeof(T);
      //   StringBuilder sb = new StringBuilder();
      //   sb.Append($"INSERT INTO {type.Name} (");
      //   PropertyInfo[] propertyInfos = type.GetProperties();
      //   foreach (PropertyInfo p in propertyInfos)
      //   {
      //       ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
      //       if (attribute == null)
      //           continue;
      //       if (attribute.IsCreated)
      //       {
      //           if (attribute.useProperticeDefault) //使用属性本身的名字和类型
      //           {
      //               //属性名 属性类型 ，
      //               sb.Append(p.Name + ",");
      //           }
      //           else //使用自定义 名称和类型
      //           {
      //               sb.Append(attribute.FieldName + ",");
      //           }
      //       }
      //   }
      //
      //   sb.Remove(sb.Length - 1, 1);
      //   sb.Append(") VALUES ");
      //   foreach (T t in tList)
      //   {
      //       sb.Append("( ");
      //       //设置对应的值
      //       foreach (PropertyInfo p in propertyInfos)
      //       {
      //           ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
      //           if (attribute == null)
      //               continue;
      //           if (attribute.IsCreated)
      //           {
      //               if (attribute.Type == "string" || attribute.Type == "char")
      //               {
      //                   sb.Append($"'{p.GetValue(t)}'");
      //               }
      //               else
      //               {
      //                   sb.Append(p.GetValue(t)); //获取指定对象的属性值
      //               }
      //
      //               sb.Append(",");
      //           }
      //       }
      //
      //       sb.Remove(sb.Length - 1, 1);
      //       sb.Append("),");
      //   }
      //
      //   sb.Remove(sb.Length - 1, 1);
      //   _sqlComm.CommandText = sb.ToString();
      //   int ret = _sqlComm.ExecuteNonQuery();
      // //  _sqlComm.Dispose();
      //   return ret;
      int count = 0;
      foreach (T t in tList)
      {
          count+= _sqlDbConn.Insert(t);
      }
      return count;
    }

    /// <summary>
    /// 给数据库对应类型的表中 添加多组数据
    /// </summary>
    /// <param name="tList">数据集合实例</param>
    /// <typeparam name="T">对应表的数据类型</typeparam>
    /// <returns></returns>
    public static int Insert<T>(this SqlDbConnect _sqlDbConn,params T[] tList) where T : BaseSqliteData
    {
        if (tList == null || tList.Length == 0)
        {
            Debug.LogError("Insert()  参数错误！！！");
            return -1;
        }

        // Type type = typeof(T);
        // StringBuilder sb = new StringBuilder();
        // sb.Append($"INSERT INTO {type.Name} (");
        // PropertyInfo[] propertyInfos = type.GetProperties();
        // foreach (PropertyInfo p in propertyInfos)
        // {
        //     ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
        //     if (attribute == null)
        //         continue;
        //     if (attribute.IsCreated)
        //     {
        //         if (attribute.useProperticeDefault) //使用属性本身的名字和类型
        //         {
        //             //属性名 属性类型 ，
        //             sb.Append(p.Name + ",");
        //         }
        //         else //使用自定义 名称和类型
        //         {
        //             sb.Append(attribute.FieldName + ",");
        //         }
        //     }
        // }
        //
        // sb.Remove(sb.Length - 1, 1);
        // sb.Append(") VALUES ");
        // foreach (T t in tList)
        // {
        //     sb.Append("( ");
        //     //设置对应的值
        //     foreach (PropertyInfo p in propertyInfos)
        //     {
        //         ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
        //         if (attribute == null)
        //             continue;
        //         if (attribute.IsCreated)
        //         {
        //             if (attribute.Type == "string" || attribute.Type == "char")
        //             {
        //                 sb.Append($"'{p.GetValue(t)}'");
        //             }
        //             else
        //             {
        //                 sb.Append(p.GetValue(t)); //获取指定对象的属性值
        //             }
        //
        //             sb.Append(",");
        //         }
        //     }
        //
        //     sb.Remove(sb.Length - 1, 1);
        //     sb.Append("),");
        // }
        //
        // sb.Remove(sb.Length - 1, 1);
        // _sqlComm.CommandText = sb.ToString();
        // int ret = _sqlComm.ExecuteNonQuery();
        // _sqlComm.Dispose();
        // return ret;
        int count = 0;
        foreach (T t in tList)
        {
            count+= _sqlDbConn.Insert(t);
        }
        return count;
    }

    #endregion

    #region 删除数据

    /// <summary>
    /// 根据表中的id字段 删除表中的的某一条数据
    /// </summary>
    /// <param name="id">Id</param>
    /// <typeparam name="T">与表名相同的数据类型</typeparam>
    /// <returns></returns>
    public static int DeleteById<T>(this SqlDbConnect _sqlDbConn,int id) where T : BaseSqliteData
    {
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        string sql = $"DELETE FROM {typeof(T).Name} where Id={id}";
        _sqlComm.CommandText = sql;
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    /// <summary>
    /// 根据对应表中的Id 字段删除多条数据 
    /// </summary>
    /// <param name="ids">Id 集合</param>
    /// <typeparam name="T">与表名相同的数据类型</typeparam>
    /// <returns></returns>
    public static int DeleteByIds<T>(this SqlDbConnect _sqlDbConn,List<int> ids) where T : BaseSqliteData
    {
        int count = 0;
        foreach (int id in ids)
        {
            count += _sqlDbConn.DeleteById<T>(id);
        }

        return count;
    }

    /// <summary>
    /// 根据对应表中的Id 字段删除多条数据 
    /// </summary>
    /// <param name="ids">Id 集合</param>
    /// <typeparam name="T">与表名相同的数据类型</typeparam>
    /// <returns></returns>
    public static int DeleteByIds<T>(this SqlDbConnect _sqlDbConn,params int[] ids) where T : BaseSqliteData
    {
        int count = 0;
        foreach (int id in ids)
        {
            count += _sqlDbConn.DeleteById<T>(id);
        }

        return count;
    }

    /// <summary>
    ///使用sql语句 根据表中匹配到的数据 进行删除
    /// 比如 ”Id=1“  或者 “Sex=1”  
    /// </summary>
    public static  int DeleteBySql<T>(this SqlDbConnect _sqlDbConn,string sql) where T : BaseSqliteData
    {
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        string sqlstr = $"DELETE FROM {typeof(T).Name} where {sql}";
        _sqlComm.CommandText = sqlstr;
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    /// <summary>
    ///使用sql语句 多次 根据表中匹配到的数据 进行删除
    /// 比如 ”Id=1“  或者 “Sex=1”  
    /// </summary>
    public static int DeleteBySqls<T>(this SqlDbConnect _sqlDbConn,params string[] sqls) where T : BaseSqliteData
    {
        int count = 0;
        foreach (string sql in sqls)
        {
            count += _sqlDbConn.DeleteBySql<T>(sql);
        }

        return count;
    }

    #endregion

    #region 更新数据

    /// <summary>
    /// 根据id更新数据库中数据的信息
    /// </summary>
    /// <param name="t">更改后的数据 数据实例</param>
    /// <typeparam name="T">数据类型</typeparam>
    /// <returns></returns>
    public static int UpdateById<T>(this SqlDbConnect _sqlDbConn,T t) where T : BaseSqliteData
    {
        if (t == default(T))
        {
            Debug.LogError("Update()  参数错误！！！");
            return -1;
        }
 
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        
        Type type = typeof(T);
        StringBuilder sb = new StringBuilder();
        sb.Append($"UPDATE {type.Name} set ");
        PropertyInfo[] propertyInfos = type.GetProperties();
        foreach (PropertyInfo p in propertyInfos)
        {
            ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
            if (attribute == null)
                continue;
            if (attribute.IsCreated)
            {
                if (attribute.useProperticeDefault) //使用属性本身的名字和类型
                {
                    sb.Append($"{p.Name} = ");
                    // var valueType= p.GetValue(t).GetType().Name;
                    var valueType= p.PropertyType.Name;
                    if (valueType== typeof(string).Name || valueType == typeof(char).Name)
                        //属性名 属性类型 ，
                        sb.Append($"'{p.GetValue(t)}'");
                    else
                        sb.Append($"{p.GetValue(t)}");
                }
                else //使用自定义 名称和类型
                {
                    sb.Append($"{attribute.FieldName} = ");
                    if (attribute.Type == "string" || attribute.Type == "char")
                        sb.Append($"'{p.GetValue(t)}'");
                    else
                        sb.Append(p.GetValue(t));
                }

                sb.Append(",");
            }
        }

        sb.Remove(sb.Length - 1, 1);
        sb.Append($" where Id={t.Id}");

        _sqlComm.CommandText = sb.ToString();
        int ret = _sqlComm.ExecuteNonQuery();
        _sqlComm.Dispose();
        return ret;
    }

    public static int Update<T>(this SqlDbConnect _sqlDbConn,params T[] tList) where T : BaseSqliteData
    {
        if (tList == null || tList.Length == 0)
        {
            Debug.LogError("Update(list) 参数错误");
            return -1;
        }

        int count = 0;
        foreach (T data in tList)
        {
            count += _sqlDbConn.UpdateById(data);
        }
        
        return count;
    }

    #endregion

    #region 查询数据

    /// <summary>
    /// 根据数据Id查找表中的对应数据
    /// </summary>
    /// <param name="id">数据Id</param>
    /// <typeparam name="T">数据类型   表名</typeparam>
    /// <returns></returns>
    public static T SelectById<T>(this SqlDbConnect _sqlDbConn,int id) where T : BaseSqliteData
    {
        Type type = typeof(T);
        string sql = $"SELECT * FROM {type.Name} where Id= {id}";
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        _sqlComm.CommandText = sql;
        SqliteDataReader dr = _sqlComm.ExecuteReader();
        if (dr != null && dr.Read())
        {
            var temp=DataReaderToData<T>(dr);
            _sqlComm.Dispose();
            return temp;
        }
        _sqlComm.Dispose();
        return default;
    }

    /// <summary>
    /// 查询所有的数据
    /// </summary>
    /// <typeparam name="T">表名</typeparam>
    /// <returns></returns>
    public static List<T> SelectAll<T>(this SqlDbConnect _sqlDbConn) where T : BaseSqliteData
    {
        return _sqlDbConn.SelectBySql<T>(string.Empty);
    }

    /// <summary>
    /// 通过sql where查询语句 查询匹配项
    /// </summary>
    /// <param name="sqlWhere">sql where查询语句</param>
    /// <typeparam name="T">数据类  表名</typeparam>
    /// <returns></returns>
    public static List<T> SelectBySql<T>(this SqlDbConnect _sqlDbConn,string sqlWhere) where T : BaseSqliteData
    {
        List<T> ret = new List<T>();
        string sql=string.IsNullOrEmpty(sqlWhere)
            ?$"SELECT * FROM {typeof(T).Name}"
            : $"SELECT * FROM {typeof(T).Name} where {sqlWhere}";
        SqliteCommand _sqlComm= new SqliteCommand(_sqlDbConn.SqliteConn);
        _sqlComm.CommandText = sql;
        SqliteDataReader dr= _sqlComm.ExecuteReader();
        if (dr != null)
        {
            while (dr.Read())
            {
                ret.Add(DataReaderToData<T>(dr));
            }
        }
        _sqlComm.Dispose();
        return ret;
    }
    private static T DataReaderToData<T>(SqliteDataReader dr) where T : BaseSqliteData
    {
        try
        {
            List<string> fieldNames = new List<string>();
            for (int i = 0; i < dr.FieldCount; i++)
            {
                //将数据库中的变量名存储起来
                fieldNames.Add(dr.GetName(i));
            }
            Type type = typeof(T);
            T data = Activator.CreateInstance<T>();
            var properties = type.GetProperties();
            foreach (PropertyInfo p in properties)
            {
                if (!p.CanWrite) continue;
                ModelHelp attribute = p.GetCustomAttribute<ModelHelp>();
                if (attribute == null)
                    continue;
                if (attribute.IsCreated) //如果这个属性可以在数据库中创建
                {
                    //TODO 解析
                    if (attribute.useProperticeDefault) //使用属性本身的名字和类型
                    {
                        if (fieldNames.Contains(p.Name))
                        {
                            var tempValue = dr[p.Name];
                            if (p.PropertyType.Name == typeof(Single).Name)//判断对应数据属性类型是否是float类型
                            {
                                tempValue = Convert.ToSingle(tempValue);
                                p.SetValue(data,tempValue);
                            }
                            else
                            {
                                p.SetValue(data, tempValue);
                            }
                        }
                    }
                    else //使用自定义 名称和类型
                    {
                        if (fieldNames.Contains(attribute.FieldName))
                        {
                            var tempValue = dr[attribute.FieldName];
                            if (attribute.Type == "float")
                            {
                                tempValue = Convert.ToSingle(tempValue);
                                p.SetValue(data, tempValue);
                            }
                            else
                            {
                                p.SetValue(data, tempValue);
                            }
                           
                        }
                    }
                }
            }
            return data;
        }
        catch (Exception e)
        {
            Debug.LogError($"DataReaderToData() 转换出错：{typeof(T).Name}  "+e.Message);
            return null;
        }
    }

    #endregion
}